<?php
//个人信息
namespace Admin\Controller;
use Mcit\Page;

class IndexController extends BaseController {

    //导入Excel文件  方法1
    protected function _import($filename, $exts='xlsx')
    {
        $filePath = UPLOAD_PATH.$filename;
        //导入PHPExcel类库
        Vendor('PHPExcel.PHPExcel');
        //创建PHPExcel对象，注意，不能少了\
        $PHPExcel=new \PHPExcel();
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            $PHPReader=new \PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            $PHPReader=new \PHPExcel_Reader_Excel2007();
        }
        //载入文件
        $PHPExcel=$PHPReader->load($filePath);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=2;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
            }

        }
//        $this->save_import($data);
        return $data;
    }

    //导入Excel文件  方法2
    protected function _excel($filename){
        G('excel_begin');
        Vendor('PHPExcel.PHPExcel');
        $filePath = UPLOAD_PATH.$filename;
        if(empty($filePath)){
            $return['state'] = 200;
            $return['msg']   = '请确认要导入的excel表！';
            return $return;
        }
        $phpExcel = new \PHPExcel ();
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (! $PHPReader->canRead ( $filePath )) {
            $PHPReader = new \PHPExcel_Reader_Excel5 ();
            if (! $PHPReader->canRead ( $filePath )) {
                $return['state'] = 200;
                $return['msg']   = '不是EXCEL文件！';
                return $return;
            }
        }
        $PHPExcel = $PHPReader->load($filePath);
        //读表第一个工作表
        $currentSheet = $PHPExcel->getSheet(0);
        //取得最大的列号
        $allColumn = $currentSheet->getHighestColumn();
        //取得总行数
        $allRow = $currentSheet->getHighestRow();
        //定义一个数组
        $excelContent = array();
        //从第二行开始读取数据，因为第一行为列名
        for($currentRow = 2;$currentRow <= $allRow;$currentRow++){
            $machine_array = array();
            //从A列开始读取数据
            for($currentColumn = 'A';$currentColumn<= $allColumn; $currentColumn++){
                //ord()将字符转为十进制数
//                $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();
                $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue();
                // 富文本
                if( gettype($val) == 'object' ){
                    $val = $val->__toString();
                }
                $find = array(
                    'A','B','C','D','E','F','G','H','I','J','K','L','M',
                    'N','O','P','Q','R','S','T','U','V','W',"X",'Y','Z'
                );
                $replace = array(
                    'name','area_code','province','city','location_text','address',
                    'circle','type','coordtype','coordinate','floor','parking','total_par',
                    'evc_type','charge_port_type','total_ev','charge_fast_num','charge_slow_num',
                    'establish_time','booking','regulated','regulated_phone','company','oper_id','current_state'
                );
                $fieldName = str_replace($find, $replace, $currentColumn);
                //$excelContent[$currentRow][$fieldName] = iconv('utf-8','gbk', $val);
                $excelContent[$currentRow-2][$fieldName] = $val;
            }
        }
        //return $excelContent;
        G('excel_end');
//        $return['state'] = 100;
//        $return['msg']   = '导入成功';
//        $return['data']  = $excelContent;
//        $return['time']  = G('excel_begin','excel_end', 6).'秒';
//        $return['mem']   = G('excel_begin','excel_end', 'm').'kb';
//        $return['time']  = G('excel_begin','excel_end', 6).'秒';
//        $return['mem']   = G('excel_begin','excel_end', 'm').'kb';
//        return $return;
        return $excelContent;
    }

    public function index(){
       $this->display();
	}
        public function profile(){

            /*  PHPexcel 中
             时间戳转成正常日期的公式:
                C1=(A1+8*3600)/86400+70*365+19
                其中A1表示当时的1249488000时间戳数值
                其中C1就是所需的日期格式，C1单元格属性改成日期格式就可以了。
            正常日期转为时间戳格式公式：
                A1=(C1-70*365-19)*86400-8*3600
                其中C1表示正常日期时间格式；
                其中A1就是所需的时间戳格式，A1单元格属性日期格式转成常规格式就可以了。*/

            /*$filename = "tests.xlsx";
            $result = $this->_excel($filename);//方法2
    //        $result = $this->_import($filename);//方法1
    //        P($result);

            foreach($result as $v){
                $area = strlen($v['area_code']);
                if($area == 2){
                    $area = str_pad($v['area_code'],3,0,STR_PAD_LEFT);
                }elseif($area == 3){
                    $area = str_pad($v['area_code'],4,0,STR_PAD_LEFT);
                }
                $region = M("region")->where("fullname = '".$v['location_text']."'")->find();
                $data['location'] = $region['ppcode'].",".$region['pcode'].",".$region['code'];
                $data['establish_time']   =  date('Y-m-d', ($v['establish_time']-70*365-19)*86400-8*3600);//正常日期转为时间戳格式公式(PHPexcel)
                $data['area_code']        =  $area;
                $data['name']             = $v['name'];
                $data['type']             =  $v['type'];
                $data['total_par']        =  $v['total_par'];
                $data['evc_type']         =  $v['evc_type'];
                $data['charge_port_type'] =  $v['charge_port_type'];
                $data['total_ev']         =  $v['total_ev'];
                $data['charge_fast_num']  =  $v['charge_fast_num'];
                $data['charge_slow_num']  =  $v['charge_slow_num'];
                $data['booking']          =  $v['booking'];
                $data['oper_id']          =  $v['oper_id'];
                $data['floor']            =  $v['floor'];
                $data['province']         =  $v['province'];
                $data['city']             =  $v['city'];
                $data['address']          =  $v['address'];
                $data['circle']           =  $v['circle'];
                $data['coordtype']        =  $v['coordtype'];
                $data['coordinate']       =  $v['coordinate'];
                $data['parking']          =  $v['parking'];
                $data['current_state']    =  $v['current_state'];
                $data['regulated']        =  $v['regulated'];
                $data['regulated_phone']  =  $v['regulated_phone'];
                $data['company']          =  $v['company'];
                $rr = M("plant")->add($data);
            }
            if($rr){
                echo "保存成功！";
            }else{
                echo M("plant")->getLastSql()."<br />";
            }*/
            //站点统计 运营中 在建中 规划中
            $this->plant_online = M("plant")->count();
            $this->plant_building = M("plant")->where("current_state = 2")->count();
            $this->plant_coming = M("plant")->where("current_state = 3")->count();
            //电桩状态统计
            $this->charge = D("State")->Relation(true)->where("charge=1")->count();
            $this->release = D("State")->Relation(true)->where("charge=0 and bespeak=0 and error=0 and cable=0")->count();
            $this->error = D("State")->Relation(true)->where("error=1")->count();
            $this->charAll = D("State")->Relation(true)->where(1)->count();//总数

            //充电总次数
            $this->sumCharge  = M("bill_total")->where(1)->sum('total');
            //预约总次数
            $this->sumBespeak = M("bespeak_total")->where(1)->sum("total");
            //碳排放总量
            $countCarbon      = M("bill_total")->field("SUM(electricity) as electricity")->where(1)->select();
            foreach($countCarbon as $k => $v){
                $this->sumCarbon  = sprintf("%.2f", $v['electricity'] * 1.174);
            }

            $this->display();
        }
    //地图
    public  function  map(){

		 $plant=M()->query('select * from ev_plant where current_state=1');//  and city like "成都%"
        $this->assign("count",count($plant));

        $shishi=M()->query('select * from ev_plant where current_state=1 and is_active=1');//  and city like "成都%"
        $this->assign("shishi",count($shishi));

        $yushe=M()->query('select * from ev_plant where current_state<>1');//  and city like "%成都%"
        $this->assign("yushe",count($yushe));

        $this->china = D("Region")->getChinaArea();
        $this->display();
    }
    public  function command(){
        $this->display();
    }


    //站点模糊查询
    public  function map_name(){
        if(!empty($_GET['name'])){
            $name = I("name");
        }
        $plant=M()->query("select id,name,current_state,address,is_active,coordinate from ev_plant where name like '%".$name."%'");
//        echo M()->getLastSql();die;
        $AddMarkString = '';
        for( $i=0;$i<count($plant);$i++){
            if(!empty($plant[$i]['coordinate'])){
                $arr=explode(",",$plant[$i]['coordinate']);
                $AddMarkString .="mark".$plant[$i]['id'].",".$arr[0].",".$arr[1].",".$plant[$i]['name'].",".$plant[$i]['current_state'].",".$plant[$i]['address'].",".$plant[$i][id].",".$plant[$i]['is_active'].",".$plant[$i]['current_state']."+";
            }
        }
        if($AddMarkString){
            echo $AddMarkString;
        }else{
            echo error_get_last();
        }
    }

    //省市区站点查询
    public  function map_loc(){

        if(!empty($_GET['location_f_1'])){

            if(I('location_s_1') == 110100 || I('location_s_1') == 120100 || I('location_s_1') == 310100 || I('location_s_1') == 500100){
                if(I('location_s_1') == I('location_t_1')-1){
                    $location = I('location_f_1').",".I('location_s_1');
                }else{
                    $location = I('location_f_1').",".I('location_s_1').",".I('location_t_1');
                }
            }else if(I('location_s_1') == I('location_t_1')-1){
                $location = I('location_f_1').",".I('location_s_1');
            }else{
                $location = I('location_f_1').",".I('location_s_1').",".I('location_t_1');
            }
        }
        $plant=M()->query("select id,name,current_state,address,is_active,coordinate from ev_plant where location like '%$location%'");
//        echo M()->getLastSql();die;
        $AddMarkString = '';
        for( $i=0;$i<count($plant);$i++){
            if(!empty($plant[$i]['coordinate'])){
                $arr=explode(",",$plant[$i]['coordinate']);
                $AddMarkString .="mark".$plant[$i]['id'].",".$arr[0].",".$arr[1].",".$plant[$i]['name'].",".$plant[$i]['current_state'].",".$plant[$i]['address'].",".$plant[$i][id].",".$plant[$i]['is_active'].",".$plant[$i]['current_state']."+";
            }
        }
        if($AddMarkString){
            echo $AddMarkString;
        }else{
            echo error_get_last();
        }
    }

    //查询所有站点地图坐标
    public  function map_now(){
        $plant=M()->query('select id,name,current_state,address,is_active,coordinate from ev_plant');
        $AddMarkString = '';
        for( $i=0;$i<count($plant);$i++){
            if(!empty($plant[$i]['coordinate'])){
                $arr=explode(",",$plant[$i]['coordinate']);
                $AddMarkString .="mark".$plant[$i]['id'].",".$arr[0].",".$arr[1].",".$plant[$i]['name'].",".$plant[$i]['current_state'].",".$plant[$i]['address'].",".$plant[$i][id].",".$plant[$i]['is_active'].",".$plant[$i]['current_state']."+";
            }
        }
        if($AddMarkString){
            echo $AddMarkString;
        }else{
            echo error_get_last();
        }
    }

    //实时查询地图坐标
    public  function map_sel(){
        $hotflag=$_REQUEST['hotflag'];//表示哪一种热点度的站点，0或NULL表示全部，1，2，3，4，5
        if (!isset($hotflag) || empty($hotflag) || !$hotflag){
            $hotflag=0;
        }
    
    
        $table="ev_bill_total";
    
        $c="{$table}.state=1 and {$table}.site_id<>''";
    

    
        $sql_sum_all="select sum({$table}.total) as total from {$table} join ev_plant on ev_plant.id={$table}.site_id where {$c}";
    
        $sql_sum_by_site="select sum({$table}.total) as total, {$table}.site_id as site_id from {$table} join ev_plant on ev_plant.id={$table}.site_id where {$c}  group by {$table}.site_id ";
    
        $total_sum_all=M()->query($sql_sum_all);

        $total_sum_by_site=M()->query($sql_sum_by_site);
    
/*         echo M()->getLastSql();
        die; */

    
        $all_total=0;
        foreach ($total_sum_all as $k=>$v){
            $all_total=$v['total'];
    
        }
    
         
        $plant_3_months=array();
        foreach ($total_sum_by_site as $k=>$v){
            $plant_3_months["{$v['site_id']}"]=$v["total"];
    
        }
    
    
/*     echo $all_total;
    echo "<br>";
    var_dump($plant_3_months);
    die; */
        $sqll = "SELECT sum(a.total) as total,(sum(a.charge_time) / 3600) as charge_time,sum(a.electricity) as electricity,sum(a.amount) as amount,c.id as ev_plant_id
        from {$table} a
        LEFT JOIN ev_char b on b.key_link = a.key_link
        LEFT JOIN ev_plant c on c.id = b.ev_plant_id
        WHERE a.state = 1
        AND a.currentTime >= date_sub(curdate(), interval 3 month)
        AND a.currentTime < curdate()
        GROUP BY c.id
        ORDER BY sum(a.total) DESC";
        $tongji = M()->query($sqll);
    
        $map['current_state']   = 1;
        $map['is_active']       = 1;
    
    
    
    
        $plant = M("plant")->field("id,name,address,coordinate,current_state,is_active")->where($map)->select();
         

        $AddMarkString = '';
    
        $percent_num=floor($all_total/5);
        //$plant_new=array();
        foreach($plant as $k=>$v){
 
            if (isset($plant_3_months[$v['id']])){
    
                $tmp_total=$plant_3_months[$v['id']];
    
                //echo $tmp_total."<br>";
                if ($tmp_total>0 and $tmp_total<=$percent_num){
                    if ($hotflag!=0 && $hotflag!=5){
                        unset($plant[$k]);
                        continue;
                    }
                    $plant[$k]['state_i']=5;
                     
                }elseif ($tmp_total>$percent_num and $tmp_total<=$percent_num*2){
                    if ($hotflag!=0 && $hotflag!=4){
                        unset($plant[$k]);
                        continue;
                    }
                    $plant[$k]['state_i']=4;
                }elseif ($tmp_total>$percent_num*2 and $tmp_total<=$percent_num*3){
                    if ($hotflag!=0 && $hotflag!=3){
                        unset($plant[$k]);
                        continue;
                    }
                    $plant[$k]['state_i']=3;
                }elseif ($tmp_total>$percent_num*3 and $tmp_total<=$percent_num*4){
                    if ($hotflag!=0 && $hotflag!=2){
                        unset($plant[$k]);
                        continue;
                    }
                     
                    $plant[$k]['state_i']=2;
                }elseif ($tmp_total>$percent_num*4 and $tmp_total<=$all_total){

                    if ($hotflag!=0 && $hotflag!=1){
                        unset($plant[$k]);
                        continue;
                    }
                    
                    $plant[$k]['state_i']=1;
 
                }else{
                    if ($hotflag!=0 && $hotflag!=5){
                        unset($plant[$k]);
                        continue;
                    }
                    $plant[$k]['state_i']=5;
                }
            }else{
                if ($hotflag!=0 && $hotflag!=5){
                    unset($plant[$k]);
                    continue;
                }
                $plant[$k]['state_i']=5;
            }
    
        }
    
        //var_dump($plant);
    
        foreach( $plant as $k=>$v){
            //var_dump($v['coordinate']);
            if($v['coordinate']){
                $arr=explode(",",$v['coordinate']);
                //var_dump($Arr);
                $AddMarkString .="mark".$v['id'].",".$arr[0].",".$arr[1].",".$v['name'].",".$v['current_state'].",".$v['address'].",".$v['id'].",".$v['is_active'].",".$v['state_i']."+";
            }
    
        }
    
        if($AddMarkString){
            echo $AddMarkString;
        }else{
            echo error_get_last();
        }
    }
        
    
    

    //预设站点
    public  function map_will(){
        $plant=M()->query('select id,name,current_state,address,is_active,coordinate from ev_plant where current_state<>1 and is_active<>1');
        $AddMarkString = '';
        for( $i=0;$i<count($plant);$i++){
            if(!empty($plant[$i]['coordinate'])){
                $arr=explode(",",$plant[$i]['coordinate']);
                $AddMarkString .="mark".$plant[$i]['id'].",".$arr[0].",".$arr[1].",".$plant[$i]['name'].",".$plant[$i]['current_state'].",".$plant[$i]['address'].",".$plant[$i][id].",".$plant[$i]['is_active'].",".$plant[$i]['current_state']."+";
            }
        }
        if($AddMarkString){
            echo $AddMarkString;
        }else{
            echo error_get_last();
        }
    }


    //查询当前站点，电桩，状态
    public function map_sel_state_count(){
        $plant_id=$_POST['plant_id'];
        //此站点下所有桩与其对应的状态
        $sql = "select a.evc_number,a.key_link,a.wifi,b.name as plant_name,b.address,
                c.s_key_link,c.id as state_id,c.bespeak,c.cable,c.charge,c.now_user,c.error,d.`name` as trademark
                from ev_char a
                LEFT JOIN ev_plant b on b.id = a.ev_plant_id
                LEFT JOIN char_state_now c on c.key_link = a.key_link
                LEFT JOIN ev_fac d on d.fac_login_name = a.ev_fac_code
                WHERE a.ev_plant_id <> ''
                and c.key_link <> ''
                and b.id ='$plant_id'";
        $charInfo = M()->query($sql);
        foreach($charInfo as $k => $v){
            if($v['trademark']){
                $charInfo[$k]['trademark'] = $v['trademark'];
            }else{
                $charInfo[$k]['trademark'] = "未知";
            }
        }
        /*$charInfo = M("char")->where("ev_plant_id='".$plant_id."'")->select();
        if($charInfo){
			foreach($charInfo as $k=>$v){
           $plantInfo = M("plant")->where("id='".$v['ev_plant_id']."'")->find();
           $stateInfo = D("State")->where("key_link='".$v['key_link']."'")->find();
           $charInfo[$k]['title']       = $plantInfo['name'];
           $charInfo[$k]['address']     = $plantInfo['address'];
            $fac_name = M("ev_fac")->where("fac_login_name='".$v['ev_fac_code']."'")->find();
            if($fac_name['name'] != ''){
                $charInfo[$k]['trademark']   = $fac_name['name'];
            }else{
                $charInfo[$k]['trademark']   = "未知";
            }
           $charInfo[$k]['station_key'] = $plantInfo['station_key'];
           $charInfo[$k]['stateInfo']   = $stateInfo;
        }
        echo json_encode($charInfo);
		}	*/
        echo json_encode($charInfo);
    }
    //充电总数以及预约总数
    public function chart(){
        if(IS_AJAX){
            if($_POST['type'] == "charge"){
                //最近7天充电总次数
                $charge = M("bill_total")->field("SUM(total) as total,currentTime")->where(1)->group("currentTime")->order("currentTime desc")->limit(7)->select();
                echo json_encode($charge);
            }elseif($_POST['type'] == "bespeak"){
                //最近7天总预约数
                $bespeak = M("bespeak_total")->field("SUM(total) as total,currentTime")->where(1)->group("currentTime")->order("currentTime desc")->limit(7)->select();
                echo json_encode($bespeak);
            }elseif($_POST['type'] == "carbon"){
                //最近7天碳排放
                $carbon = M("bill_total")->field("SUM(electricity) as electricity,currentTime")->where(1)->group("currentTime")->order("currentTime desc")->limit(7)->select();
                foreach($carbon as $k => $v){
                    $carbon[$k]['carbon']  = sprintf("%.2f", $v['electricity'] * 1.174);
                }
                echo json_encode($carbon);
            }
        }
    }
}